﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using System.Windows;
using System.Windows.Forms;
using System.Drawing;

namespace QQ群成员获取.Operate
{
    /// <summary>
    /// 功能说明:EXCEL转换类
    /// 创建人:SJH
    /// 日期:20191010
    /// </summary>
    public class ExcelOper
    {
        /// <summary>
        /// excelapp
        /// </summary>
        private Excel.Application _excelApp = null;
        /// <summary>
        /// 工作薄集合
        /// </summary>
        private Excel.Workbooks _books = null;
        /// <summary>
        /// 工作簿
        /// </summary>
        private Excel._Workbook _book = null;
        /// <summary>
        /// 工作表集合
        /// </summary>
        private Excel.Sheets _sheets = null;
        /// <summary>
        /// 工作表数组
        /// </summary>
        private Excel._Worksheet[] _sheet = null;
        /// <summary>
        /// 范围
        /// </summary>
        private Excel.Range _range = null;
        /// <summary>
        /// 可选值
        /// </summary>
        private object _optionalValue = Missing.Value;
        /// <summary>
        /// 字段列表
        /// </summary>
        private List<string> _strFldlst = null;
        #region 将dataset转换为Excel,导出到指定目录:名称为空 则用户自定义路径
        /// <summary>
        /// 输出dataset到excel
        /// </summary>
        /// <param name="ds">数据集</param>
        /// <param name="strFilePath">保存文件路径</param>
        /// <param name="strFldLst">字段列表</param>
        /// <returns>true/false</returns>
        public bool GetExcelByDataSet(DataSet ds, string strFilePath, List<string> strFldLst = null)
        {
            if (string.IsNullOrWhiteSpace(strFilePath) || (strFilePath.ToLower().EndsWith(".xls") == false &&
                strFilePath.ToLower().EndsWith(".xlsx") == false) || ds == null || ds.Tables.Count == 0)
            {
                return false;
            }
            _strFldlst = strFldLst;
            bool success = true;
            if (ds != null)
            {
                try
                {
                    CreateExcelRef(ds);
                    WriteData(ds);
                    Save(strFilePath);
                    success = true;
                }
                catch (Exception e)
                {
                    success = false;
                }
                finally
                {
                    if (_excelApp != null)
                    {
                        _excelApp.Quit();
                    }
                    ReleaseObject(_sheets);
                    ReleaseObject(_book);
                    ReleaseObject(_books);
                    ReleaseObject(_excelApp);
                }
            }

            return success;

        }

        private void Save(string strFilePath)
        {
            if (!string.IsNullOrWhiteSpace(strFilePath))
                _book.SaveAs(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);

        }
        private void ReleaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
        private void WriteData(DataSet ds)
        {
            if (ds == null)
                return;
            object[,] objData = null;
            for (int v = 0; v < ds.Tables.Count; v++)
            {
                objData = new object[ds.Tables[v].Rows.Count, ds.Tables[v].Columns.Count];
                for (int j = 0; j < ds.Tables[v].Rows.Count; j++)
                {
                    var item = ds.Tables[v].Rows[j];
                    for (int i = 0; i < ds.Tables[v].Columns.Count; i++)
                    {
                        var y = item[i];
                        objData[j, i] = (y == null || string.IsNullOrWhiteSpace(y.ToString())) ? "" : ("\t" + y).ToString();
                    }
                }
                AddExcelRows(v, "A2", ds.Tables[v].Rows.Count, ds.Tables[v].Columns.Count, objData);
                AutoFitColumns(v, "A1", ds.Tables[v].Rows.Count, ds.Tables[v].Columns.Count);
                //设置表头
                for (int i = 0; i < ds.Tables[v].Columns.Count; i++)
                {

                    Excel.Range headRange = _sheet[v].Cells[1, i + 1] as Excel.Range;//获取表头单元格

                    if (ds.Tables[v].Columns[i].DataType == typeof(System.String)
                        || ds.Tables[v].Columns[i].DataType == typeof(System.DateTime))
                    {
                        headRange.NumberFormatLocal = "@";
                    }

                    headRange.Value2 = ds.Tables[v].Columns[i].ColumnName;//设置单元格文本

                    headRange.Font.Name = "宋体";//设置字体

                    headRange.Font.Size = 14;//字体大小
                    headRange.Font.Bold = true;//加粗显示

                    headRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中

                    headRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中

                    headRange.ColumnWidth = 20;//设置列宽
                    headRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
                    headRange.Borders.Weight = Excel.XlBorderWeight.xlThick;//边框常规粗细

                }
                //内容自适应
                _sheet[v].Cells.EntireRow.AutoFit();
            }




        }
        private void AutoFitColumns(int item, string startRange, int rowCount, int colCount)
        {
            _range = _sheet[item].get_Range(startRange, _optionalValue);
            _range = _range.get_Resize(rowCount, colCount);
            _range.Columns.AutoFit();
        }

        private void AddExcelRows(int item, string startRange, int rowCount, int colCount, object values)
        {
            _range = _sheet[item].get_Range(startRange, _optionalValue);
            _range = _range.get_Resize(rowCount, colCount);
            _range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
            _range.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细
            _range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//水平居中
            _range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//垂直居中
            _range.set_Value(_optionalValue, values);
            _range.WrapText = true;
            _range.Orientation = 0;
        }

        /// <summary>
        /// 创建EXCEL结构
        /// </summary>
        /// <param name="ds">要素集</param>
        /// <param name="filepath">excel文件名</param>
        private void CreateExcelRef(DataSet ds)
        {
            if (ds == null)
                return;
            _excelApp = new Excel.Application();

            _books = (Excel.Workbooks)_excelApp.Workbooks;
            _book = (Excel._Workbook)(_books.Add(_optionalValue));
            _sheets = (Excel.Sheets)_book.Worksheets;
            _sheet = new Excel._Worksheet[ds.Tables.Count];
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                if (_sheets.Count >= i + 1)
                    _sheet[i] = (Excel._Worksheet)(_sheets.get_Item(i + 1));
                else
                    _sheet[i] = (Excel._Worksheet)_sheets.Add();
                _sheet[i].Name = ds.Tables[i].TableName;
            }

        }
        #endregion

        #region 将excel转换为dataset,并且生成的dataset已去除空行
        /// <summary>
        /// 将EXCEL转换为DataTable
        /// </summary>
        /// <param name="excelPath">excel文件路径</param>
        /// <param name="type">统计类型:0为通用 1为权属代码表的完整性 2为身份错误信息</param>
        /// <returns></returns>
        public static DataSet GetDataSetByEecel(string excelPath, int type = 0, List<string> strTabNames = null, bool bTrimEmpty = true)
        {
            DataSet ds = new DataSet();

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Excel.Sheets sheets;
            object oMissiong = System.Reflection.Missing.Value;
            Excel.Workbook workbook = null;
            try
            {
                if (app == null) return null;
                workbook = app.Workbooks.Open(excelPath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
                sheets = workbook.Worksheets;
                int sheetCount = sheets.Count;
                DataTable dt = null;
                for (int num = 1; num <= sheetCount; num++)
                {
                    dt = new DataTable();
                    //将数据读入到DataTable中
                    Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(num);//读取表

                    if (worksheet == null || worksheet.Name.Contains("填写规范"))
                    {
                        dt = null;
                        continue;
                    }
                    if (strTabNames != null && strTabNames.Contains(worksheet.Name) == false)
                    {
                        continue;
                    }
                    dt.TableName = worksheet.Name;
                    int iRowCount = worksheet.UsedRange.Rows.Count;
                    int iColCount = worksheet.UsedRange.Columns.Count;
                    //生成列头
                    for (int i = 0; i < iColCount; i++)
                    {
                        var headerTxt = "";
                        if (type == 0)
                        {
                            //无列头
                            break;
                        }
                        if (type == 1)
                        {
                            //列头行数为1
                            headerTxt = ((Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
                        }
                        if (type == 2)
                        {
                            headerTxt = ((Excel.Range)worksheet.Cells[2, i + 1]).Text.ToString();
                        }
                        if (dt.Columns.Contains(headerTxt))
                            headerTxt = headerTxt + "_1";//重复行名称会报错。
                        dt.Columns.Add(new DataColumn(headerTxt, typeof(string)));
                    }
                    //生成行数据
                    Excel.Range range;
                    int rowIdx = -1;
                    //通用(列标题行数为0，统计数据从第一行开始)
                    if (type == 0)
                    {
                        rowIdx = 1;
                    }
                    //统计权属代码表的完整性(列标题行数为1,统计数据从第二行开始)
                    if (type == 1)
                    {
                        rowIdx = 2;
                    }
                    //身份错误信息(列标题行数为2,统计数据从第三行开始)
                    if (type == 2)
                    {
                        rowIdx = 3;
                    }
                    for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int iCol = 1; iCol <= iColCount; iCol++)
                        {
                            range = (Excel.Range)worksheet.Cells[iRow, iCol];
                            dr[iCol - 1] = range.Value2 == null ? "" : bTrimEmpty ? range.Text.ToString().Trim() : range.Text.ToString();
                        }
                        if (dr != null)
                        {
                            dt.Rows.Add(dr);
                        }
                    }
                    //去除dt中的空行
                    DataTable dtAfterDelNullRow = delNullRows(dt);
                    ds.Tables.Add(dtAfterDelNullRow);
                }
                return ds;
            }
            catch { return null; }
            finally
            {
                workbook.Close(false, oMissiong, oMissiong);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
            }
        }

        private static DataTable delNullRows(DataTable dt)
        {
            DataTable dtRtn = dt;
            bool isNullRow = true;
            List<DataRow> rowlist = new List<DataRow>();//记录空行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                isNullRow = true;//默认
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    //如果这一行中某个单元格数据不为空 则这一列不是空行
                    if (!string.IsNullOrWhiteSpace(dt.Rows[i][j].ToString()))
                    {
                        isNullRow = false;
                        break;
                    }
                }
                //循环完毕,当前行所有数据为空 记录当前行为空行
                if (isNullRow == true)
                {
                    rowlist.Add(dt.Rows[i]);
                }
            }
            //取出记录,将其从dt中删除
            for (int num = 0; num < rowlist.Count; num++)
            {
                dtRtn.Rows.Remove(rowlist[num]);
            }

            return dtRtn;
        }
        #endregion
    }
}

